 <?php
	/**
 * Enter description here ...
 * @author josue
 *
 * tags
 */
 
class Admindb_model extends CI_Model{

	var $TKM_CONFIG_TABLE = "CREATE TABLE IF NOT EXISTS `tkm_config` (
	  `ID_config` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	  `Equipo` varchar(15) DEFAULT NULL,
	  `Instancia` varchar(20) NOT NULL,
	  `fecha` datetime NOT NULL,
	  `tiempo` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	  `Cliente` varchar(20) DEFAULT NULL,
	  PRIMARY KEY (`ID_config`),
	  UNIQUE KEY `ID` (`ID_config`)
	) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;";
	 
	 
	 
	var $TKM_CONFIGDETAIL_TABLE = "CREATE TABLE IF NOT EXISTS `tkm_configdetail` (
	  `ID_configdetail` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	  `ID_config` bigint(20) unsigned NOT NULL,
	  `fecha` datetime NOT NULL,
	  `encabezado` varchar(35) NOT NULL,
	  `parametro` varchar(35) NOT NULL,
	  `valor` text NOT NULL,
	  PRIMARY KEY (`ID_configdetail`,`ID_config`),
	  KEY `FK_configdetail_config` (`ID_config`)
	) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=213 ;";
	
	var $TKM_DAY_TABLE = "CREATE TABLE IF NOT EXISTS `tkm_day` (
	  `tkdays` smallint(3) DEFAULT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
	 
	var $TKM_HOURS_TABLE = "CREATE TABLE IF NOT EXISTS `tkm_hours` (
	  `tkhoursofday` smallint(3) DEFAULT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
	 
	var $TKM_INTERVALO_TABLE = "CREATE TABLE IF NOT EXISTS `tkm_intervalo` (
	  `tknumdays` smallint(3) DEFAULT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
	 
	var $TKM_LOG_TABLE = "CREATE TABLE IF NOT EXISTS `tkm_log` (
	  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	  `equipo` varchar(20) NOT NULL,
	  `Instancia` varchar(20) NOT NULL,
	  `fecha` datetime NOT NULL,
	  `tiempo` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	  `Temperature_1` float(5,2) unsigned DEFAULT '0.00',
	  `Humidity_1` float(5,2) unsigned DEFAULT '0.00',
	  `Temperature_2` float(5,2) unsigned DEFAULT '0.00',
	  `Humidity_2` float(5,2) unsigned DEFAULT '0.00',
	  `Temperature_3` float(5,2) unsigned DEFAULT '0.00',
	  `Humidity_3` float(5,2) unsigned DEFAULT '0.00',
	  `Temperature_4` float(5,2) unsigned DEFAULT '0.00',
	  `Humidity_4` float(5,2) unsigned DEFAULT '0.00',
	  `PSTemperature_1` smallint(3) DEFAULT '0',
	  `PSTemperature_2` smallint(3) DEFAULT '0',
	  `Voltage_1A` float(6,3) DEFAULT '0.000',
	  `Voltage_1B` float(6,3) DEFAULT '0.000',
	  `Voltage_1C` float(6,3) DEFAULT '0.000',
	  `Current_1A` float(6,3) DEFAULT '0.000',
	  `Current_1B` float(6,3) DEFAULT '0.000',
	  `Current_1C` float(6,3) DEFAULT '0.000',
	  `Voltage_2A` float(6,3) DEFAULT '0.000',
	  `Voltage_2B` float(6,3) DEFAULT '0.000',
	  `Voltage_2C` float(6,3) DEFAULT '0.000',
	  `Current_2A` float(6,3) DEFAULT '0.000',
	  `Current_2B` float(6,3) DEFAULT '0.000',
	  `Current_2C` float(6,3) DEFAULT '0.000',
	  `Voltage_3A` float(6,3) DEFAULT '0.000',
	  `Voltage_3B` float(6,3) DEFAULT '0.000',
	  `Voltage_3C` float(6,3) DEFAULT '0.000',
	  `Current_3A` float(6,3) DEFAULT '0.000',
	  `Current_3B` float(6,3) DEFAULT '0.000',
	  `Current_3C` float(6,3) DEFAULT '0.000',
	  `Voltage_4A` float(6,3) DEFAULT '0.000',
	  `Voltage_4B` float(6,3) DEFAULT '0.000',
	  `Voltage_4C` float(6,3) DEFAULT '0.000',
	  `Current_4A` float(6,3) DEFAULT '0.000',
	  `Current_4B` float(6,3) DEFAULT '0.000',
	  `Current_4C` float(6,3) DEFAULT '0.000',
	  `Voltage_5A` float(6,3) DEFAULT '0.000',
	  `Voltage_5B` float(6,3) DEFAULT '0.000',
	  `Voltage_5C` float(6,3) DEFAULT '0.000',
	  `Current_5A` float(6,3) DEFAULT '0.000',
	  `Current_5B` float(6,3) DEFAULT '0.000',
	  `Current_5C` float(6,3) DEFAULT '0.000',
	  `Voltage_6A` float(6,3) DEFAULT '0.000',
	  `Voltage_6B` float(6,3) DEFAULT '0.000',
	  `Voltage_6C` float(6,3) DEFAULT '0.000',
	  `Current_6A` float(6,3) DEFAULT '0.000',
	  `Current_6B` float(6,3) DEFAULT '0.000',
	  `Current_6C` float(6,3) DEFAULT '0.000',
	  `Voltage_7A` float(6,3) DEFAULT '0.000',
	  `Voltage_7B` float(6,3) DEFAULT '0.000',
	  `Voltage_7C` float(6,3) DEFAULT '0.000',
	  `Voltage_8A` float(6,3) DEFAULT '0.000',
	  `Voltage_8B` float(6,3) DEFAULT '0.000',
	  `Voltage_8C` float(6,3) DEFAULT '0.000',
	  `Diesel_1` smallint(3) DEFAULT '0',
	  `Diesel_2` smallint(3) DEFAULT '0',
	  `DigitalInput_1` tinyint(1) DEFAULT '0',
	  `DigitalInput_2` tinyint(1) DEFAULT '0',
	  `DigitalInput_3` tinyint(1) DEFAULT '0',
	  `DigitalInput_4` tinyint(1) DEFAULT '0',
	  `DigitalInput_5` tinyint(1) DEFAULT '0',
	  `DigitalInput_6` tinyint(1) DEFAULT '0',
	  `DigitalInput_7` tinyint(1) DEFAULT '0',
	  `DigitalInput_8` tinyint(1) DEFAULT '0',
	  `DigitalInput_9` tinyint(1) DEFAULT '0',
	  `DigitalInput_10` tinyint(1) DEFAULT '0',
	  `DigitalInput_11` tinyint(1) DEFAULT '0',
	  `DigitalInput_12` tinyint(1) DEFAULT '0',
	  `DigitalInput_13` tinyint(1) DEFAULT '0',
	  `DigitalInput_14` tinyint(1) DEFAULT '0',
	  `DigitalInput_15` tinyint(1) DEFAULT '0',
	  `DigitalInput_16` tinyint(1) DEFAULT '0',
	  `DigitalInput_17` tinyint(1) DEFAULT '0',
	  `DigitalInput_18` tinyint(1) DEFAULT '0',
	  `DigitalInput_19` tinyint(1) DEFAULT '0',
	  `DigitalInput_20` tinyint(1) DEFAULT '0',
	  `DigitalInput_21` tinyint(1) DEFAULT '0',
	  `DigitalInput_22` tinyint(1) DEFAULT '0',
	  `DigitalInput_23` tinyint(1) DEFAULT '0',
	  `DigitalInput_24` tinyint(1) DEFAULT '0',
	  `DigitalOutput_1` tinyint(1) DEFAULT '0',
	  `DigitalOutput_2` tinyint(1) DEFAULT '0',
	  `DigitalOutput_3` tinyint(1) DEFAULT '0',
	  `DigitalOutput_4` tinyint(1) DEFAULT '0',
	  `DigitalOutput_5` tinyint(1) DEFAULT '0',
	  `DigitalOutput_6` tinyint(1) DEFAULT '0',
	  `DigitalOutput_7` tinyint(1) DEFAULT '0',
	  `DigitalOutput_8` tinyint(1) DEFAULT '0',
	  `DigitalOutput_9` tinyint(1) DEFAULT '0',
	  `DigitalOutput_10` tinyint(1) DEFAULT '0',
	  `DigitalOutput_11` tinyint(1) DEFAULT '0',
	  `DigitalOutput_12` tinyint(1) DEFAULT '0',
	  `DigitalOutput_13` tinyint(1) DEFAULT '0',
	  `DigitalOutput_14` tinyint(1) DEFAULT '0',
	  `DigitalOutput_15` tinyint(1) DEFAULT '0',
	  `DigitalOutput_16` tinyint(1) DEFAULT '0',
	  UNIQUE KEY `ID` (`ID`)
	) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10430 ;";
	
	var $TKM_LOG_FECHA_INDEX = "CREATE INDEX date_index using btree on tkm_log(fecha);";
	 
	var $TKM_MINUTOS_TABLE = "CREATE TABLE IF NOT EXISTS `tkm_minutes` (
	  `tkminute` smallint(3) DEFAULT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
	 
	var $TKM_MONTH_TABLE = "CREATE TABLE IF NOT EXISTS `tkm_month` (
	  `tkmonths` smallint(3) DEFAULT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
	 
	var $TKM_YEAR_TABLE = "CREATE TABLE IF NOT EXISTS `tkm_year` (
	  `tkyears` smallint(3) DEFAULT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
	 
	var $CONFIGDETAIL_FOREIGN = "ALTER TABLE `tkm_configdetail`
	  ADD CONSTRAINT `FK_configdetail_config` FOREIGN KEY (`ID_config`) REFERENCES `tkm_config` (`ID_config`);";
	
	var $INSERT_TKM_DAY = "INSERT INTO `tkm_day` (`tkdays`) VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31);";
	
	var $INSERT_TKM_HOUR = "INSERT INTO `tkm_hours` (`tkhoursofday`) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23);";
	
	var $INSERT_TKM_INTERVALO = "INSERT INTO `tkm_intervalo` (`tknumdays`) VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90);";
	
	var $INSERT_TKM_MINUTES = "INSERT INTO `tkm_minutes` (`tkminute`) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59);";
	
	var $INSERT_TKM_MONTH = "INSERT INTO `tkm_month` (`tkmonths`) VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);";
	
	var $INSERT_TKM_YEAR = "INSERT INTO `tkm_year` (`tkyears`) VALUES(2008),(2009),(2010),(2011),(1012);";
	
	public function __construct(){
		parent::__construct();
		$this->load->database();
	}
	
	public function create_client($username,$pass,$mysqlPass,$dbname){
		#falta validación mysql de inserciones, se considera OK todo el tiempo
		$result = array();
		
		$result["CREATE_MYSQL_USER"] = $this->_create_user($username);
		$result["CRFEATE_DATABASE"] = $this->_create_database($dbname);
		$result["SET_PERMISSIONS"] = $this->_setPermissionsOnDB($username, $dbname);
		$result["SET_PASSWORD"] = $this->_setPassword($username, $mysqlPass);
		#registramos la base de datos en la tabla tkm_db
		$data = array(
				'dbname' => $dbname,
				'dbpassword' => $mysqlPass
				);
		$this->db->insert(TKM_DB_TABLE,$data);
		$dbid = $this->db->insert_id();
		#registramos el usuario en la tabla tkm_login
		$data = array(
				'username' => $username,
				'password' => crypt($pass),
				'dbid' => $dbid
				);
		$this->db->insert(LOGIN_TABLE,$data);
		return $result;
	}

	private function _create_user($username){
		
		$query = "GRANT USAGE ON *.* to ". $username ."@\"%\"";
		$result = $this->db->simple_query($query);
		
		return $result;
	}
	
	private function _create_database($dbname){
		
		$query = "CREATE DATABASE " . $dbname .";";
		$result = $this->db->simple_query($query);
		$this->_loadSchema($dbname);		
		//conectandose a la base de datos default
		$this->load->database();
		return $result;
	}
	
	
	
	private function _loadSchema($dbname){
		$this->db->query("use ".$dbname);
		//insertando el default schema en la nueva base de datos
		//$this->load->database($db);
		$this->db->query($this->TKM_CONFIG_TABLE);
		$this->db->query($this->TKM_CONFIGDETAIL_TABLE);
		$this->db->query($this->TKM_DAY_TABLE);
		$this->db->query($this->TKM_HOURS_TABLE);
		$this->db->query($this->TKM_INTERVALO_TABLE);
		$this->db->query($this->TKM_LOG_TABLE);
		$this->db->query($this->$TKM_LOG_FECHA_INDEX);
		$this->db->query($this->TKM_MINUTOS_TABLE);
		$this->db->query($this->TKM_MONTH_TABLE);
		$this->db->query($this->TKM_YEAR_TABLE);
		$this->db->query($this->CONFIGDETAIL_FOREIGN);
		// insertando tuplas default
		$this->db->query($this->INSERT_TKM_DAY);
		$this->db->query($this->INSERT_TKM_HOUR);
		$this->db->query($this->INSERT_TKM_MINUTES);
		$this->db->query($this->INSERT_TKM_MONTH);
		$this->db->query($this->INSERT_TKM_YEAR);
		$this->db->query($this->INSERT_TKM_INTERVALO);
		
		$this->db->close();
	}
	
	private function _setPermissionsOnDB($username,$dbname){
		$query = "GRANT Insert, Select, Update, Delete ON " . $dbname . ".* TO " . $username ."@\"%\"";
		$result = $this->db->query($query);
		$this->db->simple_query("FLUSH PRIVILEGES");
	}
	
	private function _setPassword($username,$mysqlPass){
		
		$this->db->query("use mysql");
		$query = "UPDATE user SET Password=PASSWORD('".$mysqlPass."') WHERE User='".$username."';";
		$result = $this->db->query($query);
		$this->db->simple_query("FLUSH PRIVILEGES");
		$this->db->close();
		//conectandose a la base de datos default
		$this->load->database();
	}
		
}
?>